mysql> -- 显示数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dorm_db            |
| information_schema |
| java118_mysql_code |
| java_testdb        |
| mysql              |
| performance_schema |
| sys                |
| test_db2           |
| testdb             |
| yjp_mydb           |
+--------------------+
10 rows in set (0.00 sec)

mysql> -- 查看指令--》为了将sql脚本导入
mysql> \h

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command, if enabled
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file

For server side help, type 'help contents'

mysql> -- 使用的指令是\.
mysql> #例如：
mysql> \. "E:\study_javaee_project\bilibili-self-study-code\study_self\yjp_mysql_select\sql练习.sql"
ERROR:
Failed to open file '"E:\study_javaee_project\bilibili-self-study-code\study_self\yjp_mysql_select\sql练习.sql"', error: 22
mysql> -- 发现报错了 不知道为什么？==》插一下ai
mysql> #经过一查ai 原来报错的原因就是说 我的\是一个转义字符 需要使用\转义 或者改为/
mysql> \. "E:\\study_javaee_project\\bilibili-self-study-code\\study_self\\yjp_mysql_select\\sql练习.sql"
ERROR:
Failed to open file '"E:\\study_javaee_project\bilibili-self-study-code\study_self\yjp_mysql_select\sql练习.sql"', error: 22
mysql> -- 此时还是包错
mysql> \. E:\\study_javaee_project\\bilibili-self-study-code\\study_self\\yjp_mysql_select\\sql练习.sql
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 3780 (HY000): Referencing column 'tno' and referenced column 'tno' in foreign key constraint 'teacher_fk' are incompatible.
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1824 (HY000): Failed to open the referenced table 'course'
ERROR 1366 (HY000): Incorrect integer value: 't1001' for column 'tno' at row 1
ERROR 1366 (HY000): Incorrect string value: '\x80\xE5\xBF\x83' for column 'sname' at row 5
ERROR 1146 (42S02): Table 'yjp_mydb.course' doesn't exist
ERROR 1146 (42S02): Table 'yjp_mydb.sc' doesn't exist
mysql> -- 报错原因找到了就是不要加引号即可
mysql> -- 那么现在就是我的sql中出现重复主键名了 我需要处理一下
mysql> -- 外键列 tno 和它引用的主表列 tno 数据类型、长度或字符集不一致，所以无法建立外键约束。
mysql> show tables;
+--------------------+
| Tables_in_yjp_mydb |
+--------------------+
| student            |
| teacher            |
+--------------------+
2 rows in set (0.00 sec)

mysql> \. E:\\study_javaee_project\\bilibili-self-study-code\\study_self\\yjp_mysql_select\\sql练习.sql
Query OK, 2 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 3780 (HY000): Referencing column 'tno' and referenced column 'tno' in foreign key constraint 'teacher_fk' are incompatible.
Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1824 (HY000): Failed to open the referenced table 'course'
ERROR 1366 (HY000): Incorrect integer value: 't1001' for column 'tno' at row 1
ERROR 1366 (HY000): Incorrect string value: '\x80\xE5\xBF\x83' for column 'sname' at row 5
ERROR 1146 (42S02): Table 'yjp_mydb.course' doesn't exist
ERROR 1146 (42S02): Table 'yjp_mydb.sc' doesn't exist
mysql> show tables;
+--------------------+
| Tables_in_yjp_mydb |
+--------------------+
| student            |
| teacher            |
+--------------------+
2 rows in set (0.00 sec)

mysql> drop database yjp_mydb;
Query OK, 2 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dorm_db            |
| information_schema |
| java118_mysql_code |
| java_testdb        |
| mysql              |
| performance_schema |
| sys                |
| test_db2           |
| testdb             |
+--------------------+
9 rows in set (0.00 sec)

mysql> \. E:\\study_javaee_project\\bilibili-self-study-code\\study_self\\yjp_mysql_select\\sql练习.sql
Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)

ERROR 1366 (HY000): Incorrect string value: '\x80\xBB' for column 'tname' at row 2
ERROR 1366 (HY000): Incorrect string value: '\x80\xE5\xBF\x83' for column 'sname' at row 5
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yjp_mydb`.`course`, CONSTRAINT `teacher_fk` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`))
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yjp_mydb`.`sc`, CONSTRAINT `student_fk` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))
mysql> show tables;
+--------------------+
| Tables_in_yjp_mydb |
+--------------------+
| course             |
| sc                 |
| student            |
| teacher            |
+--------------------+
4 rows in set (0.00 sec)

mysql> \. E:\\study_javaee_project\\bilibili-self-study-code\\study_self\\yjp_mysql_select\\sql练习.sql
Query OK, 4 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)

ERROR 1366 (HY000): Incorrect string value: '\x80\xBB' for column 'tname' at row 2
ERROR 1366 (HY000): Incorrect string value: '\x80\xE5\xBF\x83' for column 'sname' at row 5
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yjp_mydb`.`course`, CONSTRAINT `teacher_fk` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`))
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yjp_mydb`.`sc`, CONSTRAINT `student_fk` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`))
mysql> -- 没办法 一直报错 于是我就直接慢慢执行
mysql> -- 创建表并实现查询操作
mysql> #创建数据库
mysql> drop database if exists yjp_mydb;
Query OK, 4 rows affected (0.04 sec)

mysql> create database if not exists yjp_mydb charset='utf8mb4';
Query OK, 1 row affected (0.00 sec)

mysql> #使用数据库
mysql>
mysql> use yjp_mydb;
Database changed
mysql>
mysql>
mysql> show tables;
Empty set (0.00 sec)

mysql> -- 创建教师表
mysql> drop table if exists teacher;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table if not exists teacher(
    ->     tno varchar(100) primary key comment '教师编号',
    ->     tname varchar(255) comment '教师姓名'
    -> ) comment '教师表';
Query OK, 0 rows affected (0.02 sec)

mysql> -- 创建学生表
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table if not exists student(
    ->     sno varchar(100) primary key comment '学生编号',
    ->     sname varchar(255) not null comment '学生姓名',
    ->     sage tinyint unsigned comment '学生年龄',
    ->     ssex enum('男','女') comment '学生性别'
    -> ) comment '学生表';
Query OK, 0 rows affected (0.01 sec)

mysql> -- 创建课程表
mysql> drop table if exists course;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table if not exists course(
    ->     cno varchar(100) primary key comment '课程编号',
    ->     cname varchar(200) not null comment '课程名称',
    ->     tno varchar(100),
    ->     constraint teacher_fk foreign key(tno) references teacher(tno)
    -> ) comment '课程表';
Query OK, 0 rows affected (0.03 sec)

mysql> -- 创建学生选课表
mysql> drop table if exists sc;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table if not exists sc(
    ->     sno varchar(100),
    ->     cno varchar(100),
    ->     score float(3,1),
    ->     primary key(sno, cno),
    ->     constraint student_fk foreign key(sno) references student(sno),
    ->     constraint course_fk foreign key(cno) references course(cno)
    -> ) comment '学生选课表';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> # 添加数据
mysql> -- 教师表
mysql> insert into teacher values
    -> ('t1001','叶平'),
    -> ('t1002','也总'),
    -> ('t1003','张国平');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> -- 学生表
mysql> insert into student values
    -> ('1001','张三',20,'男'),
    -> ('1002','李斯',20,'男'),
    -> ('1003','小李',20,'女'),
    -> ('1004','好乐',22,'女'),
    -> ('1005','开心',21,'男');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- 课程表
mysql> insert into course values
    -> ('1001','j2ee','t1001'),
    -> ('1002','运筹学','t1002'),
    -> ('1003','java','t1003'),
    -> ('1004','.NET','t1001'),
    -> ('1005','操作系统','t1003');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- 学生选课表
mysql> insert into sc values
    ->     ('1001','1001',98),
    ->     ('1001','1002',90),
    ->     ('1002','1001',93),
    ->     ('1002','1002',92),
    ->     ('1003','1001',67),
    ->     ('1003','1002',69),
    ->     ('1003','1003',70),
    ->     ('1004','1002',65),
    ->     ('1005','1004',88),
    ->     ('1004','1003',78);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> -- 会发现一步一步执行没问题
mysql> -- 那么应该是我们的代码中有中文的关系吧
mysql>